import json
import logging
import datetime
import os
import time

from django.http import JsonResponse, QueryDict
from django.shortcuts import render

# Create your views here.
from django.views import View
from database.connection import Link_database
from global_file import SMS_TIME

from caiwu.extension import SubmitExpenseSQL
from caiwu.extension import DelSubmitExpenseSQL
from caiwu.extension import ExpenseSQL
from caiwu.extension import DelExpenseSQL
from caiwu.extension import FinancialPackageSQL
from caiwu.extension import WageSQL
from caiwu.extension import ToDeductedSQL
from caiwu.extension import SubmitTypeSQL
from caiwu.extension import IncomeTypeSQL
from caiwu.extension import SumIncomeSQL
from caiwu.extension import del_WageSQL
from caiwu.extension import DelSumIncomeSQL

from caiwu.take_data import submit_expense
from caiwu.take_data import expense
from caiwu.take_data import financial_package
from caiwu.take_data import personnel_wages
from caiwu.take_data import to_deducted
from caiwu.take_data import sel_generaoperattube
from caiwu.take_data import submit_type
from caiwu.take_data import income_type
from caiwu.take_data import sum_income
from caiwu.take_data import user_wage

from dateutil.relativedelta import relativedelta

from caiwu.take_data import send_SMS

import random
import calendar

import copy



logger = logging.getLogger(__name__)


def hostname():
    """判断本地与服务器"""
    sys = os.name
    if sys == 'nt':
        hostname = os.getenv('computername')
        return hostname
    elif sys == 'posix':
        host = os.popen('echo $HOSTNAME')
        try:
            hostname = host.read()
            return hostname
        finally:
            host.close()
    else:
        return 'Unkwon hostname'


class AccountEntry(View):
    """ 财务入账 """

    def post(self, request):
        shop_name = request.POST.get('shop_name')  # 店名名称
        customer = request.POST.get('customer')  # 客户
        head = request.POST.get('head')  # 负责人
        executor = request.POST.get('executor')  # 执行人
        cooperation = request.POST.get('cooperation')  # 合作方式
        service = request.POST.get('service')  # 服务费
        commission = request.POST.get('commission')  # 提成方式
        start_time = request.POST.get('start_time')  # 开始时间
        end_time = request.POST.get('end_time')  # 结束时间
        Play_date = request.POST.get('Play_date')  # 打款日期
        paragraph = request.POST.get('paragraph')  # 入款账户
        amount = request.POST.get('amount')  # 金额
        transfer_type = request.POST.get('transfer_type')  # 转账类型
        note = request.POST.get('note')  # 备注

        DB, Cursor = Link_database()
        inset_sql = """ INSERT INTO  booked
		(shop_name,customer,head,executor,cooperation,service,commission,start_time,end_time,Play_date,paragraph,amount,transfer_type,note) VALUES 
		('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') """ % (
        shop_name, customer, head, executor, cooperation, service, commission, start_time, end_time, Play_date,
        paragraph, amount, transfer_type, note)
        Cursor.execute(inset_sql)
        DB.commit()
        DB.close()
        Cursor.close()
        return JsonResponse({"code": 200, "result": "录入成功!", "data": []})


class AccountInquire(View):
    """ 入账查询 """

    def post(self, request):
        DB, Cursor = Link_database()
        account_sql = """ select *  from booked """
        try:
            Cursor.execute(account_sql)
            accountData = Cursor.fetchall()
            accountData_List = []
            for ad in accountData:
                accountData_List.append(
                    {"id": ad[0], "shop_name": ad[1], "customer": ad[2], "head": ad[3], "executor": ad[4],
                     "cooperation": ad[5], "service": ad[6], "commission": ad[7], "start_time": ad[8],
                     "end_time": ad[9], "Play_date": ad[10], "paragraph": ad[11], "amount": ad[12],
                     "transfer_type": ad[13], "note": ad[14]})
            code = 200
            result = "查询成功!"
        except:
            code = 400
            result = "操作失败!"
            accountData_List = []
        DB.close()
        Cursor.close()
        return JsonResponse({"code": 200, "result": result, "data": accountData_List})


class DeleteAccount(View):
    """ 删除入账账户 """

    def post(self, request):
        id = int(request.POST.get('id'))  # id
        DB, Cursor = Link_database()
        del_Sql = """ DELETE FROM booked WHERE id = %d """ % (id)
        try:
            Cursor.execute(del_Sql)
            DB.commit()
            code = 200
            result = "删除成功!"

        except:
            code = 400
            result = "操作数据库失败!"

        DB.close()
        Cursor.close()
        return JsonResponse({"code": code, "result": result, "data": []})


class UpdataRefund(View):
    """ 修改店铺退款 """

    def post(self, request):
        id = int(request.POST.get('id'))

        is_refund = request.POST.get('is_refund')  # 是否退款
        refund_month = time.strftime('%Y-%m-%d', time.localtime(time.time()))

        DB, Cursor = Link_database()
        Update_Sql = """ UPDATE store_refund SET is_refund = '%s', refund_month = '%s' WHERE  id = %d """ % (
        is_refund, refund_month, id)
        try:
            Cursor.execute(Update_Sql)
            DB.commit()
            code = 200
            result = "修改成功！"
        except:
            code = 400
            result = "修改失败，数据库操作失败！"
        DB.close()
        Cursor.close()
        return JsonResponse({"code": code, "result": result, "data": []})


# class Withholding(View):

# 	""" 退款数据录入 """ 

# 	def post(self,request):
# 		shop_name = request.POST.get('shop_name') #店名名称
# 		refund_time = request.POST.get('refund_time') #退款时间
# 		refund_money = request.POST.get('refund_money') #退款金额
# 		refund_bank = request.POST.get('refund_bank')#退款银行
# 		note = request.POST.get('note') #备注

# 		DB, Cursor = Link_database()
# 		inset_sql = """ INSERT INTO  deducted
# 		(shop_name,refund_time,refund_money,refund_bank,note) VALUES 
# 		('%s','%s','%s','%s','%s') """ % (shop_name,refund_time,refund_money,refund_bank,note)
# 		Cursor.execute(inset_sql)
# 		DB.commit()
# 		DB.close()
# 		Cursor.close()
# 		return JsonResponse({"code": 200, "result": "录入成功!", "data": []})

# class WithholdingInquire(View):

# 	""" 退款查询 """ 
# 	def post(self,request):
# 		DB, Cursor = Link_database()
# 		account_sql = """ select *  from deducted """
# 		try:
# 			Cursor.execute(account_sql)
# 			accountData = Cursor.fetchall()
# 			accountData_List = []
# 			for ad in accountData:
# 				accountData_List.append({"id":ad[0],"shop_name":ad[1],"refund_time":ad[2],"refund_money":ad[3],"refund_bank":ad[4],"note":ad[5]})
# 			code = 200
# 			result = "查询成功!"
# 		except:
# 			code = 400
# 			result = "操作失败!"
# 			accountData_List = []
# 		DB.close()
# 		Cursor.close()
# 		return JsonResponse({"code": 200, "result":result , "data": accountData_List})

# class DeleteWithholding(View):

# 	""" 删除退款项数据 """ 
# 	def post(self,request):
# 		id = int(request.POST.get('id')) #id
# 		DB, Cursor = Link_database()
# 		del_Sql = """ DELETE FROM deducted WHERE id = %d """ %(id)
# 		try:
# 			Cursor.execute(del_Sql)
# 			DB.commit()
# 			code = 200
# 			result = "删除成功!"

# 		except:
# 			code = 400
# 			result = "操作数据库失败!"

# 		DB.close()
# 		Cursor.close()
# 		return JsonResponse({"code": code, "result": result, "data": []})
class SubmitExpenseView(View):
    """报销视图"""

    def get(self, request):
        bx_name = request.GET.get('bx_name')
        bx_category = request.GET.get('bx_category')
        department = request.GET.get('department')
        start = request.GET.get('start')
        end = request.GET.get('end')

        try:
            if department:
                department = int(department)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '参数错误', "data": []})

        try:
            # 查询
            submit_obj = submit_expense(bx_name, bx_category, department)
            if start and end:
                submit_obj.statdate(start, end)
            query = submit_obj.mian()
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '查询错误', "data": []})

        try:
            # 数据处理
            data = []
            for i in query:
                a = {
                    "id": i[0],
                    "bx_time": time.strftime('%Y-%m-%d', time.localtime(i[1])),
                    "bx_department": i[2],
                    "bx_name": i[3],
                    "bx_category": i[4],
                    "bx_money": i[5],
                    "file_address": i[6].split('-'),
                    "note": i[7]
                }
                data.append(a)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '数据错误', "data": []})

        return JsonResponse({"code": 200, "result": 'OK', "data": data})

    def post(self, request):
        bx_department = request.POST.get('bx_department')
        bx_name = request.POST.get('bx_name')
        bx_time = request.POST.get('bx_time')
        bx_category = request.POST.get('bx_category')
        bx_money = request.POST.get('bx_money')
        file_list = request.FILES.getlist('img')
        note = request.POST.get('note')

        if not all([bx_name, bx_time, bx_category, bx_money, file_list]):
            return JsonResponse({"code": 400, 'result': '参数缺省', 'data': []})

        try:
            bx_department = int(bx_department)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '参数错误', "data": []})

        statdate = int(time.mktime(time.strptime(bx_time, '%Y-%m-%d')))

        file_address_list = []  # 照片地址
        for i in file_list:
            times = str(int(time.time())) + str(random.randint(0, 9))

            if (hostname()) == 'DESKTOP-424AROC':  # 判断是本地还是服务器
                a = './file/' + times + '.png'
            else:
                a = '/home/OA/FinancialStatements/' + times + '.png'

            try:
                with open(a, 'wb') as f:
                    f.write(i.read())
            except Exception as e:
                logger.error(e)
                return JsonResponse({"code": 400, "result": '保存错误', "data": []})
            a = 'https://oa.yuetaosem.com:85/FinancialStatements/' + times + '.png'
            file_address_list.append(a)

        file_address = '-'.join(file_address_list)

        try:
            # 存储
            SubmitExpenseSQL(statdate, bx_department, bx_name, bx_category, bx_money, file_address, note)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '保存错误', "data": []})

        return JsonResponse({"code": 200, "result": 'OK', "data": []})


class DelSubmitExpenseView(View):
    """报销删除视图"""

    def post(self, request):
        id = request.POST.get('id')

        try:
            id = int(id)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '参数错误', "data": []})

        try:
            # 删除
            DelSubmitExpenseSQL(id)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '删除错误', "data": []})

        return JsonResponse({"code": 200, "result": 'OK', "data": []})


class SubmitTypeView(View):
    """报销类别"""

    def get(self, reuqest):
        try:
            # 查询
            query = submit_type()
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '查询错误', "data": []})

        data = []
        for i in query:
            data.append(i[0])

        return JsonResponse({"code": 200, "result": 'OK', "data": data})

    def post(self, request):
        species = request.POST.get('species')

        try:
            # 存储
            SubmitTypeSQL(species)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '存储错误', "data": []})

        return JsonResponse({"code": 200, "result": 'OK', "data": []})


# class ExpenseView(View):
# 	"""退款视图"""
# 	def get(self, request):
# 		start = request.GET.get('start')
# 		end = request.GET.get('end')

# 		if not all([start, end]):
# 			return JsonResponse({"code": 400, 'result': '参数缺省', 'data': []})

# 		try:
# 			start = int(time.mktime(time.strptime(start, '%Y-%m-%d')))
# 			end = int(time.mktime(time.strptime(end, '%Y-%m-%d')))
# 		except Exception as e:
# 			logger.error(e)
# 			return JsonResponse({"code": 400, "result": '参数错误', "data": []})

# 		try:
# 			# 查询
# 			query = expense(start, end)
# 		except Exception as e:
# 			logger.error(e)
# 			return JsonResponse({"code": 400, "result": '查询错误', "data": []})

# 		try:
# 			# 数据处理
# 			data = []
# 			for i in query:
# 				a = {
# 					"id": i[0],
# 					"statdate": time.strftime('%Y-%m-%d', time.localtime(i[1])),
# 					"shop_name": i[2],
# 					"refund_time": i[3],
# 					"refund_money": i[4],
# 					"note": i[5]
# 				}
# 				data.append(a)
# 		except Exception as e:
# 			logger.error(e)
# 			return JsonResponse({"code": 400, "result": '数据错误', "data": []})

# 		return JsonResponse({"code": 200, "result": 'OK', "data": data})

# 	def post(self, request):
# 		shop_name = request.POST.get('shop_name')
# 		refund_time = request.POST.get('refund_time')
# 		refund_money = request.POST.get('refund_money')
# 		note = request.POST.get('note')

# 		if not all([shop_name, refund_time, refund_money, note]):
# 			return JsonResponse({"code": 400, "result": '参数缺省', "data": []})

# 		statdate = int(time.mktime(time.strptime(datetime.datetime.now().strftime('%Y-%m-%d'), '%Y-%m-%d')))

# 		try:
# 			# 存储
# 			ExpenseSQL(statdate, shop_name, refund_time, refund_money, note)
# 		except Exception as e:
# 			logger.error(e)
# 			return JsonResponse({"code": 400, "result": '保存错误', "data": []})

# 		return JsonResponse({"code": 200, "result": 'OK', "data": []})


# class DelExpenseView(View):
# 	"""删除退款视图"""
# 	def post(self,request):
# 		id = request.POST.get('id')

# 		try:
# 			id = int(id)
# 		except Exception as e:
# 			logger.error(e)
# 			return JsonResponse({"code": 400, "result": '参数错误', "data": []})

# 		try:
# 			# 删除
# 			DelExpenseSQL(id)
# 		except Exception as e:
# 			logger.error(e)
# 			return JsonResponse({"code": 400, "result": '删除错误', "data": []})

# 		return JsonResponse({"code": 200, "result": 'OK', "data": []})


class FinancialPackageView(View):
    """财务报表"""

    def get_1st_of_last_month(self):
        """
        获取上个月月份
        """
        today = datetime.datetime.today()
        year = today.year
        month = today.month
        if month == 1:
            month = 12
            year -= 1
        else:
            month -= 1
        res = datetime.datetime(year, month, 1).strftime("%Y-%m")
        return res

    def getMonthFirstDayAndLastDay(self, year, month):
        """ 获取某月的日期范围 """
        irstDayWeekDay, monthRange = calendar.monthrange(year, month)
        firstDay = datetime.datetime(year=year, month=month, day=1).strftime("%Y-%m-%d")
        lastDay = datetime.datetime(year=year, month=month, day=monthRange).strftime("%Y-%m-%d")
        return [firstDay, lastDay]

    def getYearDay(self, year):
        """ 获取某年的第一天和最后一天 """

        firstDayWeekDay, monthRange = calendar.monthrange(year, 12)

        # 获取当月的第一天
        firstDay = str(year) + '-01-01'
        lastDay = datetime.datetime(year=year, month=12, day=monthRange).strftime("%Y-%m-%d")

        return [firstDay, lastDay]

    def timestr2stamp(self, timestr):
        """ 时间字符串转时间戳 """
        try:
            timeArray = time.strptime(timestr, "%Y-%m-%d")
        except:
            timeArray = time.strptime(timestr, "%Y-%m")
        timeStamp = int(time.mktime(timeArray))
        return timeStamp

    def json2js_files(self, jsondata):
        file_name = self.get_1st_of_last_month()
        js_str = json.dumps(jsondata, ensure_ascii=False).encode("utf-8")
        if (hostname()) == 'DESKTOP-424AROC':  # 判断是本地还是服务器
            file_address = './file/' + file_name + '.json'
        else:
            file_address = '/home/OA/FinancialStatements/' + file_name + '.json'  # 文件存放位置
        with open(file_address, 'wb') as f:
            f.write(js_str)

        get_address = 'https://oa.yuetaosem.com:85/FinancialStatements/' + file_name + '.json'  # 文件读取位置

        return {'get_address': get_address, 'file_name': file_name}

    def getjson(self):
        date = self.get_1st_of_last_month()  # 查询某月的财务报表，比如：2018-12
        year = int(date.split("-")[0])
        month = int(date.split("-")[1])
        dates = self.getMonthFirstDayAndLastDay(year, month)

        DB, Cursor = Link_database()

        ########## 收支明细
        # 入账
        account_sql = """ select transfer_type,	sum(amount) from booked where Play_date Between '%s' and '%s' group by transfer_type """ % (
        dates[0], dates[1])
        Cursor.execute(account_sql)
        data = Cursor.fetchall()
        dataList = []
        dataDict = {}
        sum_amount = 0
        for i in data:
            sum_amount += i[1]
            dataList.append({"style": i[0], "amount": i[1], "remarks": ""})
        dataDict['input'] = {'data': dataList, "inputsum_amount": sum_amount}

        ##	报销
        account_sql = """ select bx_category,sum(bx_money) from submit_expense where statdate Between %d and %d group by bx_category """ % (
        self.timestr2stamp(dates[0]), self.timestr2stamp(dates[1]))
        Cursor.execute(account_sql)
        bx_list = []
        dadian = 0
        ServiceCharge = 0
        Taxation = 0
        ManagementSum = 0
        bx_data = Cursor.fetchall()
        for i in bx_data:
            if i[0] == "打点":
                dadian += i[1]
                continue
            if i[0] == "手续费":
                ServiceCharge += i[1]
                continue
            if i[0] == "税费":
                Taxation += i[1]
                continue

            ManagementSum += i[1]
            bx_list.append({"style": i[0], "amount": i[1], "remarks": ""})

        ## 退款累计
        account_sql = """ select sum(refund_amount) from store_refund where refund_month Between '%s' and '%s' """ % (
        dates[0], dates[1])
        Cursor.execute(account_sql)
        tk_data = Cursor.fetchone()  # [10321.0]
        outputsum_amount = ManagementSum + ServiceCharge + tk_data[0] + Taxation + dadian
        dataDict['output'] = {"ManagementCost": bx_list,
                              "FinancialCost": [{"amount": ServiceCharge, "style": "手续费", "remarks": ""}],
                              "OtherCost": [{"amount": tk_data[0], "style": "服务费退款", "remarks": ""},
                                            {"amount": Taxation, "style": "税费", "remarks": ""},
                                            {"amount": dadian, "sytle": "打点费", "remarks": ""}],
                              "outputsum_amount": outputsum_amount}
        dataDict['sz_balance'] = dataDict['input']['inputsum_amount'] - dataDict['output']['outputsum_amount']  # 收支余额

        # 货币余额统计
        # 计算上个月日期
        if month > 1:
            up_date = str(year) + '-' + str(month - 1)
        else:
            up_date = str(year - 1) + '-' + str(12)

        account_sql = """ select amount from currency_balance where date = '%s' """ % (up_date)
        Cursor.execute(account_sql)
        qc_balance = int(Cursor.fetchone()[0])
        dataDict['currency_balance'] = qc_balance + dataDict['sz_balance']
        dataDict['qc_balance'] = qc_balance

        # 将本月的货币余额存入系统中(如果存在就不保存，如果不存在就保存)
        account_sql = """ select amount from currency_balance where date = '%s' """ % (date)
        Cursor.execute(account_sql)
        try:
            now_balance = Cursor.fetchone()[0]  # 有数，不保存
        except:
            inset_sql = """ INSERT INTO  currency_balance(date,amount) VALUES ('%s','%s') """ % (
            date, str(dataDict['currency_balance']))
            Cursor.execute(inset_sql)
            DB.commit()

        ################ 损益明细
        # 本月收入
        month_sql = """ select category,sum(month_service_money) from sum_income where statdate Between %d and %d group by category """ % (
        self.timestr2stamp(dates[0]), self.timestr2stamp(dates[1]))
        Cursor.execute(month_sql)
        sy_data = Cursor.fetchall()
        sy_list = []
        sy_details = {}
        by_amount = 0
        for i in sy_data:
            by_amount += i[1]
            sy_list.append({"style": i[0], "amount": i[1], "remarks": ""})

        # 本年收入
        year_date = self.getYearDay(year)
        year_sql = """ select sum(month_service_money) from sum_income where statdate Between %d and %d """ % (
        self.timestr2stamp(year_date[0]), self.timestr2stamp(year_date[1]))
        Cursor.execute(year_sql)
        year_income = Cursor.fetchone()[0]
        sy_details['input'] = {"data": sy_list, "m_income": by_amount, "y_income": year_income}

        # 本月费用支出
        bx_acount = 0
        bx_detalils_list = []
        for i in bx_data:
            if i[0] == "职工薪酬":  # 计算本月的工资和，不用取报销的数据
                statdate = int(self.timestr2stamp(date))
                m_sql = """ select sum(real_wages) from wage where statdate=%d """ % (statdate)
                Cursor.execute(m_sql)
                m_wages = Cursor.fetchone()[0]
                bx_acount += m_wages
                bx_detalils_list.append({"style": i[0], "amount": m_wages, "remarks": ""})
                continue

            bx_acount += i[1]
            bx_detalils_list.append({"style": i[0], "amount": i[1], "remarks": ""})

        # 本年费用支出
        year_sql = """ select sum(bx_money) from submit_expense where statdate Between %d and %d """ % (
        self.timestr2stamp(year_date[0]), self.timestr2stamp(year_date[1]))
        Cursor.execute(year_sql)
        year_output = Cursor.fetchone()[0]
        sy_details["output"] = {'data': bx_detalils_list, 'm_output': bx_acount, 'y_output': year_output}

        # 利润
        m_profit = by_amount - bx_acount
        y_profit = year_income - year_output
        sy_details["profit"] = {"m_profit": m_profit, "y_profit": y_profit}

        return {"sz_details": dataDict, "sy_details": sy_details}

    def get(self, request):
        page = request.GET.get('page')

        # 更新财务报表数据
        try:
            page = (int(page) - 1) * 20
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '参数错误', "data": []})
        try:
            # 查询
            query = financial_package(page)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '查询错误', "data": []})

        try:
            # 数据
            data = []
            for i in query:
                a = {
                    "id": i[0],
                    "statdate": i[1],
                    "file_name": i[2],
                    "file_address": i[3]
                }
                data.append(a)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '数据错误', "data": []})

        return JsonResponse({"code": 200, "result": 'OK', "data": data})

    def post(self, request):

        statdate = str(int(time.time()))

        try:
            jsondata = self.getjson()
        except:
            return JsonResponse({"code": 400, "result": '请先录入报表需要的财务数据！', "data": []})

        save_info = self.json2js_files(jsondata)

        try:
            # 存储
            query = FinancialPackageSQL(statdate, save_info['file_name'], save_info['get_address'])
            if not query:
                return JsonResponse({"code": 400, "result": '已存在最新财务报表', "data": []})
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '存储错误', "data": []})

        return JsonResponse({"code": 200, "result": 'OK', "data": []})


class UpdateFinancial(View):
    """ 修改财务报表： 1.更换之前的json文件数据（1.文件名称，2.文件数据）"""

    def post(self, request):
        js_str = request.POST.get('js_str').encode("utf-8")
        file_name = request.POST.get('file_name')

        # 先存新文件，在删除老文件
        if (hostname()) == 'DESKTOP-424AROC':  # 判断是本地还是服务器
            file_address = './file/' + file_name + '.json'
        else:
            file_address = '/home/OA/FinancialStatements/' + file_name + '.json'  # 文件存放位置
        with open(file_address, 'wb') as f:
            f.write(js_str)
        return JsonResponse({"code": 200, "result": 'OK', "data": []})


class StoreEntry(View):
    """ 店铺录入 """

    def post(self, request):
        shop_name = request.POST.get('shop_name')  # 店铺名称
        shop_url = request.POST.get('shop_url')  # 店铺链接
        head = request.POST.get('head')  # 负责人
        executor = request.POST.get('executor')  # 执行人
        cooperation = request.POST.get('cooperation')  # 合作方式
        service = request.POST.get('service')  # 服务费
        commission = request.POST.get('commission')  # 提成方式
        start_time = request.POST.get('start_time')  # 开始时间
        end_time = request.POST.get('end_time')  # 结束时间
        amount = request.POST.get('amount')  # 已收金额
        contract_no = request.POST.get('contract_no')  # 合同编号
        project_type = request.POST.get('project_type')  # 项目类型
        service_status = request.POST.get('service_status')  # 服务状态
        contract_address = request.FILES.get('contract_address')  # 合同

        times = str(int(time.time()))

        if (hostname()) == 'DESKTOP-424AROC':  # 判断是本地还是服务器
            file_address = './file/' + times + '.pdf'
        else:
            file_address = '/home/OA/FinancialStatements/' + times + '.pdf'

        try:
            with open(file_address, 'wb') as f:
                f.write(contract_address.read())
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '保存错误', "data": []})

        file_address = 'https://oa.yuetaosem.com:85/FinancialStatements/' + times + '.pdf'

        DB, Cursor = Link_database()
        inset_sql = """ INSERT INTO  shop_info
			(shop_name,	shop_url,head,executor,cooperation,service,commission,start_time,end_time,amount,contract_no,project_type,service_status,contract_address) 
			VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') """ % (
            shop_name, shop_url, head, executor, cooperation, service, commission, start_time, end_time, amount,
            contract_no, project_type, service_status, file_address)
        try:
            Cursor.execute(inset_sql)
            DB.commit()
            code = 200
            result = "录入成功！"
        except:
            code = 400
            result = "操作数据库失败!"

        DB.close()
        Cursor.close()
        return JsonResponse({"code": code, "result": result, "data": []})


# class UpdataStore(View):

class UpdataStore(View):
    """ 修改店铺记录 """

    def post(self, request):
        id = int(request.POST.get('id'))  # 店铺信息存储唯一标识
        shop_name = request.POST.get('shop_name')  # 店铺名称
        shop_url = request.POST.get('shop_url')  # 店铺链接
        head = request.POST.get('head')  # 负责人
        executor = request.POST.get('executor')  # 执行人
        cooperation = request.POST.get('cooperation')  # 合作方式
        service = request.POST.get('service')  # 服务费
        commission = request.POST.get('commission')  # 提成方式
        start_time = request.POST.get('start_time')  # 开始时间
        end_time = request.POST.get('end_time')  # 结束时间
        amount = request.POST.get('amount')  # 已收金额
        contract_no = request.POST.get('contract_no')  # 合同编号
        project_type = request.POST.get('project_type')  # 项目类型
        service_status = request.POST.get('service_status')  # 服务状态
        contract_address = request.FILES.get('contract_address')  # 合同

        times = str(int(time.time()))

        if (hostname()) == 'DESKTOP-424AROC':  # 判断是本地还是服务器
            file_address = './file/' + times + '.doc'
        else:
            file_address = '/home/OA/FinancialStatements/' + times + '.doc'

        try:
            with open(file_address, 'wb') as f:
                f.write(contract_address.read())
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '保存错误', "data": []})

        file_address = 'https://oa.yuetaosem.com:85/FinancialStatements/' + times + '.doc'

        DB, Cursor = Link_database()
        Update_Sql = """ UPDATE shop_info SET shop_name = '%s',	shop_url = '%s', head = '%s',executor = '%s',cooperation = '%s',service = '%s',commission = '%s',start_time = '%s',end_time = '%s',amount = '%s',contract_no = '%s',project_type = '%s',service_status = '%s',contract_address = '%s' WHERE id = %d """ % (
            shop_name, shop_url, head, executor, cooperation, service, commission, start_time, end_time, amount,
            contract_no, project_type, service_status, file_address, id)
        try:
            Cursor.execute(Update_Sql)
            DB.commit()
            code = 200
            result = "修改成功！"
        except:
            code = 400
            result = "修改失败，数据库操作失败！"
        DB.close()
        Cursor.close()
        return JsonResponse({"code": code, "result": result, "data": []})


class GetShop(View):
    """ 获取全部店铺数据 """

    def post(self, request):
        DB, Cursor = Link_database()
        Attendance_Sql = """ select id,shop_name, shop_url,head, executor,	cooperation,service,commission,	start_time,	end_time,amount,contract_no,project_type,service_status,contract_address from shop_info """
        Cursor.execute(Attendance_Sql)
        AttendanceData = Cursor.fetchall()
        AttendanceList = []
        for ad in AttendanceData:
            # print (ad)
            AttendanceList.append({"id": ad[0], "shop_name": ad[1], "shop_url": ad[2], "head": ad[3], "executor": ad[4],
                                   "cooperation": ad[5], "service": ad[6], "commission": ad[7], "start_time": ad[8],
                                   "end_time": ad[9], "amount": ad[10], "contract_no": ad[11], "project_type": ad[12],
                                   "service_status": ad[13], "contract_address": ad[14], })

        if AttendanceList != []:
            code = 200
            result = "成功！"
        else:
            code = 400
            result = "操作数据库失败!"
        return JsonResponse({"code": code, "result": result, "data": AttendanceList})


class WageView(View):
    """工资视图"""

    def get(self, request):
        user_id = request.GET.get('user')
        department = request.GET.get('department')
        start_time = request.GET.get('start_time')
        end_time = request.GET.get('end_time')

        a = str(datetime.date.today() - relativedelta(months=+1))[:-3]  # 上月时间
        try:
            if user_id:
                user_id = int(user_id)
            if department:
                department = int(department)  # 部门id
            if start_time and end_time:
                is_start = True
                start_time = int(time.mktime(time.strptime(start_time, '%Y-%m')))
                end_time = int(time.mktime(time.strptime(end_time, '%Y-%m')))
            else:
                is_start = False
                start_time = int(time.mktime(time.strptime(a, '%Y-%m')))
                end_time = int(time.mktime(time.strptime(a, '%Y-%m')))
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '参数错误', "data": []})
        try:
            # 查询, 数据整理
            wages_obj = personnel_wages(user_id, department, start_time, end_time, is_start)
            data = wages_obj.deal_with()
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '查询或数据错误', "data": []})

        return JsonResponse({"code": 200, "result": 'OK', "data": data})

    def post(self, request):
        user_id = request.POST.get('user')
        basis_salary = request.POST.get('basis_salary')
        # birthday_allowance = request.POST.get('birthday_allowance')
        subsidies = request.POST.get('subsidies')
        # late = request.POST.get('late')
        # business_allowance = request.POST.get('business_allowance')
        service_fee = request.POST.get('service_fee')
        sales_some = request.POST.get('sales_some')
        performance = request.POST.get('performance')
        social = request.POST.get('social')
        statdate = request.POST.get('statdate')
        company_social = request.POST.get('company_social')
        # be_attendance = request.POST.get('be_attendance')
        # full_time = request.POST.get('full_time')

        try:
            user_id = int(user_id)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '参数错误', "data": []})

        try:
            # 存储
            wage_obj = WageSQL(user_id, basis_salary, subsidies, service_fee,
                               sales_some, performance, social, statdate, company_social)
            q = wage_obj.deal_with()
            if not q:
                return JsonResponse({"code": 400, "result": '此用户本月考勤还未录入', "data": []})
            elif q == 2:
                return JsonResponse({"code": 400, "result": '此用户 {}月的工资已录入'.format(statdate), "data": []})
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '存储错误', "data": []})

        return JsonResponse({"code": 200, "result": 'OK', "data": []})

    def delete(self, request):
        delete = QueryDict(request.body)
        id = delete.get('id')

        try:
            id = int(id)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '参数错误', "data": []})

        try:
            # 删除
            del_WageSQL(id)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '删除错误', "data": []})

        return JsonResponse({"code": 200, "result": '删除成功', "data": []})


class Del_deducted(View):
    """ 删除代扣款项数据 """

    def post(self, request):
        id = int(request.POST.get('id'))  # id
        DB, Cursor = Link_database()
        del_Sql = """ DELETE FROM to_deducted WHERE id = %d """ % (id)
        try:
            Cursor.execute(del_Sql)
            DB.commit()
            code = 200
            result = "删除成功!"

        except:
            code = 400
            result = "操作数据库失败!"

        DB.close()
        Cursor.close()
        return JsonResponse({"code": code, "result": result, "data": []})


class ToDeductedView(View):
    """待扣款视图"""

    def get(self, request):
        start_time = request.GET.get('start')
        end_time = request.GET.get('end')

        try:
            start_time = int(time.mktime(time.strptime(start_time, '%Y-%m')))
            end_time = int(time.mktime(time.strptime(end_time, '%Y-%m')))
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '时间参数错误', "data": []})

        try:
            # 查询
            query = to_deducted(start_time, end_time)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '查询错误', "data": []})

        try:
            # 数据
            data = []
            for i in query:
                a = {
                    "id": i[0],
                    "statdate": time.strftime('%Y-%m', time.localtime(i[1])),
                    "company": i[2],
                    "project": i[3],
                    "money": i[4],
                    "take_back": i[5],
                    "collection_date": i[6],
                    "collection_account": i[7]
                }
                data.append(a)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '数据错误', "data": []})

        return JsonResponse({"code": 200, "result": 'OK', "data": data})

    def post(self, request):
        statdate = request.POST.get('statdate')
        company = request.POST.get('company')
        project = request.POST.get('project')
        money = request.POST.get('money')
        take_back = request.POST.get('take_back')
        collection_date = request.POST.get('collection_date')
        collection_account = request.POST.get('collection_account')

        try:
            statdate = int(time.mktime(time.strptime(statdate, '%Y-%m')))
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '时间参数错误', "data": []})

        try:
            # 存储
            ToDeductedSQL(statdate, company, project, money, take_back, collection_date, collection_account)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, "result": '存储错误', "data": []})

        return JsonResponse({"code": 200, "result": 'OK', "data": []})


class GeneraOperatTubeView(View):
    """ 账户管理查询 """

    def get(self, request):
        page = request.GET.get('page')

        try:
            page = (int(page) - 1) * 20
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, 'result': '参数错误', "data": []})

        try:
            # 查询
            query = sel_generaoperattube(page)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, 'result': '查询错误', "data": []})

        try:
            # 数据
            data = []
            for i in query:
                a = {
                    "id": i[0],
                    "store_name": i[1],
                    "project_type": i[2],
                    "chief_inspecto": i[3],
                    "director": i[4],
                    "operator": i[5],
                    "start_time": i[6],
                    "end_time": i[7],
                    "account_entry": i[8],
                    "amount_account": i[9],
                    "renewal_time": i[10],
                    "customer_info": i[11],
                    "subscription_info": i[12],
                    "contract_status": i[13],
                    "store_status": i[14],
                    "email": i[15]
                }
                data.append(a)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, 'result': '数据错误', "data": []})

        return JsonResponse({"code": 200, 'result': 'OK', "data": data})


# class FinancialStatements(View):
# 	""" 财务报表  """
# 	def getMonthFirstDayAndLastDay(self,year,month):
# 		""" 获取某月的日期范围 """
# 		irstDayWeekDay, monthRange = calendar.monthrange(year, month)
# 		firstDay = datetime.datetime(year=year, month=month, day=1).strftime("%Y-%m-%d")
# 		lastDay = datetime.datetime(year=year, month=month, day=monthRange).strftime("%Y-%m-%d")
# 		return [firstDay, lastDay]

# 	def getYearDay(self,year):
# 		""" 获取某年的第一天和最后一天 """

# 		firstDayWeekDay, monthRange = calendar.monthrange(year, 12)

# 		# 获取当月的第一天
# 		firstDay = str(year) +'-01-01'
# 		lastDay = datetime.datetime(year=year, month=12, day=monthRange).strftime("%Y-%m-%d")

# 		return [firstDay, lastDay]

# 	def timestr2stamp(self,timestr):
# 		""" 时间字符串转时间戳 """
# 		try:
# 			timeArray = time.strptime(timestr, "%Y-%m-%d")
# 		except:
# 			timeArray = time.strptime(timestr, "%Y-%m")
# 		timeStamp = int(time.mktime(timeArray))
# 		return timeStamp

# 	def post(self,request):
# 		date = request.POST.get('date')  #查询某月的财务报表，比如：2018-12
# 		year = int(date.split("-")[0])
# 		month = int(date.split("-")[1])
# 		dates = self.getMonthFirstDayAndLastDay(year,month)

# 		DB, Cursor = Link_database()

# 		########## 收支明细
# 		# 入账
# 		account_sql = """ select transfer_type,	sum(amount) from booked where Play_date Between '%s' and '%s' group by transfer_type """ %(dates[0],dates[1])
# 		Cursor.execute(account_sql)
# 		data = Cursor.fetchall()
# 		dataList = []   
# 		dataDict = {}
# 		sum_amount = 0
# 		for i in data:
# 			sum_amount += i[1]
# 			dataList.append({"style":i[0],"amount":i[1],"remarks":""})
# 		dataDict['input'] = {'data':dataList,"inputsum_amount":sum_amount}

# 		##	报销
# 		account_sql = """ select bx_category,sum(bx_money) from submit_expense where statdate Between %d and %d group by bx_category """ %(self.timestr2stamp(dates[0]),self.timestr2stamp(dates[1]))
# 		Cursor.execute(account_sql)
# 		bx_list = []
# 		dadian = 0
# 		ServiceCharge = 0
# 		Taxation = 0
# 		ManagementSum = 0
# 		bx_data = Cursor.fetchall()
# 		for i in bx_data:
# 			if i[0] == "打点":
# 				dadian += i[1]
# 				continue
# 			if i[0] == "手续费":
# 				ServiceCharge += i[1]
# 				continue
# 			if i[0] == "税费":
# 				Taxation += i[1]
# 				continue

# 			ManagementSum += i[1]
# 			bx_list.append({"style":i[0],"amount":i[1],"remarks":""})


# 		## 退款累计
# 		account_sql = """ select sum(refund_amount) from store_refund where refund_month Between '%s' and '%s' """ %(dates[0],dates[1])
# 		Cursor.execute(account_sql)
# 		tk_data = Cursor.fetchone()  #[10321.0]
# 		outputsum_amount = ManagementSum+ServiceCharge+tk_data[0]+Taxation+dadian
# 		dataDict['output'] = {"ManagementCost":bx_list,"FinancialCost":[{"amount":ServiceCharge,"style":"手续费","remarks":""}],"OtherCost":[{"amount":tk_data[0],"style":"服务费退款","remarks":""},{"amount":Taxation,"style":"税费","remarks":""},{"amount":dadian,"sytle":"打点费","remarks":""}],"outputsum_amount":outputsum_amount}
# 		dataDict['sz_balance'] = dataDict['input']['inputsum_amount']-dataDict['output']['outputsum_amount'] # 收支余额

# 		# 货币余额统计
# 		#计算上个月日期
# 		if month>1:
# 			up_date = str(year) +'-'+ str(month-1)
# 		else:
# 			up_date = str(year-1) +'-'+ str(12)

# 		account_sql = """ select amount from currency_balance where date = '%s' """ %(up_date)
# 		Cursor.execute(account_sql)
# 		qc_balance = int(Cursor.fetchone()[0])
# 		dataDict['currency_balance'] = qc_balance+dataDict['sz_balance']
# 		dataDict['qc_balance'] = qc_balance

# 		# 将本月的货币余额存入系统中(如果存在就不保存，如果不存在就保存)
# 		account_sql = """ select amount from currency_balance where date = '%s' """ %(date)
# 		Cursor.execute(account_sql)
# 		try:
# 			now_balance = Cursor.fetchone()[0] # 有数，不保存
# 		except:
# 			inset_sql = """ INSERT INTO  currency_balance(date,amount) VALUES ('%s','%s') """ % (date,str(dataDict['currency_balance']))
# 			Cursor.execute(inset_sql)
# 			DB.commit()

# 		################ 损益明细
# 		# 本月收入
# 		month_sql = """ select category,sum(month_service_money) from sum_income where statdate Between %d and %d group by category """ %(self.timestr2stamp(dates[0]),self.timestr2stamp(dates[1]))
# 		Cursor.execute(month_sql)
# 		sy_data = Cursor.fetchall()
# 		sy_list = []
# 		sy_details = {}
# 		by_amount = 0
# 		for i in sy_data:
# 			by_amount += i[1]
# 			sy_list.append({"style":i[0],"amount":i[1],"remarks":""})


# 		# 本年收入
# 		year_date = self.getYearDay(year)
# 		year_sql = """ select sum(month_service_money) from sum_income where statdate Between %d and %d """ %(self.timestr2stamp(year_date[0]),self.timestr2stamp(year_date[1]))
# 		Cursor.execute(year_sql)
# 		year_income = Cursor.fetchone()[0]
# 		sy_details['input'] = {"data":sy_list,"m_income":by_amount,"y_income":year_income}


# 		# 本月费用支出
# 		bx_acount = 0
# 		bx_detalils_list = []
# 		for i in bx_data:
# 			if i[0] == "职工薪酬":  # 计算本月的工资和，不用取报销的数据
# 				statdate = int(self.timestr2stamp(date))
# 				m_sql = """ select sum(real_wages) from wage where statdate=%d """ %(statdate)
# 				Cursor.execute(m_sql)
# 				m_wages = Cursor.fetchone()[0]
# 				bx_acount += m_wages
# 				bx_detalils_list.append({"style":i[0],"amount":m_wages,"remarks":""})
# 				continue

# 			bx_acount += i[1]
# 			bx_detalils_list.append({"style":i[0],"amount":i[1],"remarks":""})

# 		# 本年费用支出
# 		year_sql = """ select sum(bx_money) from submit_expense where statdate Between %d and %d """ %(self.timestr2stamp(year_date[0]),self.timestr2stamp(year_date[1]))
# 		Cursor.execute(year_sql)
# 		year_output = Cursor.fetchone()[0]
# 		sy_details["output"] = {'data':bx_detalils_list,'m_output':bx_acount,'y_output':year_output}


# 		# 利润
# 		m_profit = by_amount - bx_acount
# 		y_profit = year_income - year_output
# 		sy_details["profit"] = {"m_profit":m_profit,"y_profit":y_profit}

# 		return JsonResponse({"code": 200, 'result': 'OK', "data": {"sz_details":dataDict,"sy_details":sy_details}})


class IncomeTypeView(View):
    """收入类别表"""

    def get(self, request):
        try:
            # 查询
            query = income_type()
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, 'result': '查询错误', "data": []})

        data = []
        for i in query:
            a = {
                "id": i[0],
                "category": i[1]
            }
            data.append(a)

        return JsonResponse({"code": 200, 'result': 'OK', "data": data})

    def post(self, request):
        category = request.POST.get('category')

        try:
            # 存储
            IncomeTypeSQL(category)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, 'result': '存储错误', "data": []})

        return JsonResponse({"code": 200, 'result': '添加成功', "data": []})


class SumIncomeView(View):
    """总收入表"""

    def get(self, request):
        category = request.GET.get('category')
        start_time = request.GET.get('start_time')
        end_time = request.GET.get('end_time')

        try:
            a = str(datetime.date.today() - relativedelta(months=+1))[:-3]  # 上月时间
            # if category:
            #     category = int(category)
            if start_time and end_time:
                is_start = True
                start_time = int(time.mktime(time.strptime(start_time, '%Y-%m')))
                end_time = int(time.mktime(time.strptime(end_time, '%Y-%m')))
            else:
                is_start = False
                start_time = int(time.mktime(time.strptime(a, '%Y-%m')))
                end_time = int(time.mktime(time.strptime(a, '%Y-%m')))
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, 'result': '参数错误', "data": []})

        try:
            # 查询, 数据处理
            income_obj = sum_income(category, start_time, end_time, is_start)
            data = income_obj.mian()
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, 'result': '查询错误', "data": []})

        return JsonResponse({"code": 200, 'result': 'OK', "data": data})

    def post(self, request):
        category = request.POST.get('category')
        project_name = request.POST.get('project_name')
        signing_time = request.POST.get('signing_time')
        received_service_day = request.POST.get('received_service_day')
        start_time = request.POST.get('start_time')
        end_time = request.POST.get('end_time')
        service_money = request.POST.get('service_money')
        operation = request.POST.get('operation')
        month_service_day = request.POST.get('month_service_day')
        already_paid = request.POST.get('already_paid')
        month_service_money = request.POST.get('month_service_money')
        a_service_day = request.POST.get('a_service_day')
        remaining_day = request.POST.get('remaining_day')
        a_service_money = request.POST.get('a_service_money')
        remaining_money = request.POST.get('remaining_money')
        note = request.POST.get('note')
        statdate = request.POST.get('statdate')

        try:
            # category = int(category)
            statdate = int(time.mktime(time.strptime(statdate, '%Y-%m')))
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, 'result': '参数错误', "data": []})

        try:
            # 存储
            SumIncomeSQL(category, project_name, signing_time, received_service_day, start_time, end_time,
                         service_money,
                         operation, month_service_day, already_paid, month_service_money, a_service_day, remaining_day,
                         a_service_money,
                         remaining_money, note, statdate)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, 'result': '存储错误', "data": []})

        return JsonResponse({"code": 200, 'result': 'OK', "data": []})

    def delete(self, request):
        delete = QueryDict(request.body)
        id = delete.get('id')

        try:
            id = int(id)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, 'result': '参数错误', "data": []})

        try:
            # 删除
            DelSumIncomeSQL(id)
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, 'result': '删除错误', "data": []})

        return JsonResponse({"code": 200, 'result': '删除成功', "data": []})


class SendSMSView(View):
    """工资短信"""

    def post(self, request):
        a = str(datetime.date.today() - relativedelta(months=+1))[:-3]
        month_time = int(time.mktime(time.strptime(a, '%Y-%m')))
        r = SMS_TIME  # 上次发送时间
        s = time.time()
        if s - r < 259200:  # 259200 三十天秒数
            return JsonResponse({"code": 400, 'result': '{} 工资已发送过了，请勿多次发送'.format(a), "data": []})

        global SMS_TIME
        SMS_TIME = s

        try:
            # 发送短信
            sms_obj = send_SMS(month_time)
            sms_obj.deal_with()
        except Exception as e:
            logger.error(e)
            SMS_TIME = r
            return JsonResponse({"code": 400, 'result': '发送失败', "data": []})

        return JsonResponse({"code": 200, 'result': '所有员工工资提醒已发送成功！', "data": []})


class UserWageView(View):
    """工资录入页面"""

    def get(self, request):
        try:
            # 查询
            u_obj = user_wage()
            data = u_obj.deal_with()
        except Exception as e:
            logger.error(e)
            return JsonResponse({"code": 400, 'result': '查询失败', "data": []})

        return JsonResponse({"code": 200, 'result': 'OK', "data": data})
